package com.iwhalecloud.bss.kite.cucc.service.batch;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.UUID;
import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.collect.Lists;
import com.iwhalecloud.bss.kite.client.dto.DataResult;
import com.iwhalecloud.bss.kite.common.constant.ErrorConsts;
import com.iwhalecloud.bss.kite.common.constant.KeyConsts;
import com.iwhalecloud.bss.kite.common.util.KiteListUtils;
import com.iwhalecloud.bss.kite.common.util.KiteMapUtils;
import com.iwhalecloud.bss.kite.common.util.KiteStringUtils;
import com.iwhalecloud.bss.kite.common.util.SeqUtils;
import com.iwhalecloud.bss.kite.cucc.client.api.batch.IBatchConfigService;
import com.iwhalecloud.bss.kite.cucc.client.api.goods.IGoodsService;
import com.iwhalecloud.bss.kite.cucc.client.api.oss.IAliyunOssService;
import com.iwhalecloud.bss.kite.cucc.client.dto.batch.KiteBatchConfig;
import com.iwhalecloud.bss.kite.cucc.client.dto.batch.MergedResult;
import com.iwhalecloud.bss.kite.cucc.client.dto.goods.AttrDTO;
import com.iwhalecloud.bss.kite.cucc.dataservice.mapper.batch.KiteBatchConfigMapper;
import com.iwhalecloud.bss.kite.cucc.service.util.ExcelUtil;
import com.iwhalecloud.bss.kite.dataservice.cache.OfferCache;
import com.iwhalecloud.bss.kite.dataservice.cache.OfferDetailCache;
import com.iwhalecloud.bss.kite.dataservice.cache.ProdAttrCache;
import com.iwhalecloud.bss.kite.dataservice.entity.spec.Offer;
import com.iwhalecloud.bss.kite.dataservice.entity.spec.OfferDetail;
import com.iwhalecloud.bss.kite.dataservice.entity.spec.ProdAttr;
import com.iwhalecloud.bss.kite.manager.api.IQuerySceneInstService;
import com.iwhalecloud.bss.kite.manager.inst.KiteOfferInst;
import com.iwhalecloud.bss.kite.manager.inst.KiteSceneInst;
import com.ztesoft.bss.common.util.I18nUtil;
import com.ztesoft.bss.common.util.SpringUtil;
import com.ztesoft.zsmart.core.util.JsonUtil;

import tk.mybatis.mapper.entity.Example;

@Service
@Transactional(rollbackFor = Exception.class)
public class BatchConfigService implements IBatchConfigService {

    @Autowired
    private IAliyunOssService aliyunOssService;

    @Autowired
    private KiteBatchConfigMapper kiteBatchConfigMapper;

    @Autowired
    private IQuerySceneInstService querySceneInstService;

    @Override
    public PageInfo<KiteBatchConfig> queryKiteBatchConfig(Map<String, Object> query) {
        String pageNum = KiteMapUtils.getString(query, "pageNum");
        String pageSize = KiteMapUtils.getString(query, "pageSize");
        pageNum = !KiteStringUtils.isEmpty(pageNum) ? pageNum : "1";
        pageSize = !KiteStringUtils.isEmpty(pageSize) ? pageSize : "10";
        Integer intpageNum = Integer.parseInt(pageNum);
        Integer intPageSize = Integer.parseInt(pageSize);
        PageHelper.startPage(intpageNum, intPageSize);
        List<KiteBatchConfig> list = kiteBatchConfigMapper.queryKiteBatchConfig(query);
        // 展示序号
        for (int i = 0; i < list.size(); i++) {
            KiteBatchConfig config = list.get(i);
            config.setNumber(String.valueOf((intpageNum -1) * intPageSize + i + 1));
        }
        return new PageInfo<>(list);
    }

    @Override
    public List<KiteBatchConfig> queryKiteBatchConfigForList(Map<String, Object> query) {
        return kiteBatchConfigMapper.queryKiteBatchConfig(query);
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
    @Override
    public DataResult<String> updateKiteBatchConfig(KiteBatchConfig kiteBatchConfig) {
        String fileName = kiteBatchConfig.getTmplFileName();
        String fileType = KiteStringUtils.substring(fileName, fileName.lastIndexOf('.') + 1);
        if (!KiteStringUtils.equalsAny(fileType, "xls", "xlsx")) {
            fileName = fileName + ".xls";
        }
        kiteBatchConfig.setTmplFileName(fileName);
        checkTmplFileName(kiteBatchConfig);
        kiteBatchConfig.setTmplAttrJson(geTmplAttrJson(kiteBatchConfig));
        kiteBatchConfigMapper.updateByPrimaryKeySelective(kiteBatchConfig);
        return ErrorConsts.SUCCESS.getResult(kiteBatchConfig.getConfigId().toString());
    }

    @Override
    public DataResult<String> deleteKiteBatchConfig(String configId) {
        Example example = new Example(KiteBatchConfig.class);
        example.createCriteria().andEqualTo("configId", configId);
        KiteBatchConfig kiteBatchConfig = kiteBatchConfigMapper.selectOneByExample(example);
        LocalDateTime localDateTime = LocalDateTime.now();
        kiteBatchConfig.setStatusCd("1100");
        kiteBatchConfig.setStatusDate(localDateTime);
        kiteBatchConfigMapper.updateByPrimaryKeySelective(kiteBatchConfig);
        return ErrorConsts.SUCCESS.getResult(kiteBatchConfig.getConfigId().toString());
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
    @Override
    public DataResult<String> saveKiteBatchConfig(KiteBatchConfig newKiteBatchConfig) {
//        Assert.hasText(newKiteBatchConfig.getTmplFileUrl(), "TmplFileUrl can not be null");
        Assert.hasText(newKiteBatchConfig.getApplyRegionCode(), "ApplyRegionCode can not be null");
        Assert.hasText(newKiteBatchConfig.getCatagoryId(), "CatagoryId can not be null");
        Assert.hasText(newKiteBatchConfig.getOfferId(), "OfferId can not be null");

        String fileName = newKiteBatchConfig.getTmplFileName();
        String fileType = KiteStringUtils.substring(fileName, fileName.lastIndexOf('.') + 1);
        if (!KiteStringUtils.equalsAny(fileType, "xls", "xlsx")) {
            fileName = fileName + ".xls";
        }
        newKiteBatchConfig.setTmplFileName(fileName);

        checkTmplFileName(newKiteBatchConfig);

        LocalDateTime localDateTime = LocalDateTime.now();
        newKiteBatchConfig.setConfigId(Long.valueOf(SeqUtils.getNext("SEQ_KITE_BATCH_CONFIG_CONFIG_ID")));
        newKiteBatchConfig.setStatusCd("1000");
        newKiteBatchConfig.setCreateDate(localDateTime);
        newKiteBatchConfig.setStatusDate(localDateTime);

        newKiteBatchConfig.setTmplAttrJson(geTmplAttrJson(newKiteBatchConfig));

        kiteBatchConfigMapper.insert(newKiteBatchConfig);
        StringBuffer result = new StringBuffer();
        result.append("模板保存成功，模板ID：").append(newKiteBatchConfig.getConfigId()).append("，可稍后在查询页面查看。");
        return ErrorConsts.SUCCESS.getResult(result.toString());
    }

    private String geTmplAttrJson(KiteBatchConfig newKiteBatchConfig) {
        if (KiteStringUtils.isEmpty(newKiteBatchConfig.getShowAttrJon())) {
            return null;
        }
        // 基础业务信息
        List<ProdAttr> baseInfo = new ArrayList<>();
        // A端业务信息
        List<ProdAttr> aInfo = new ArrayList<>();
        // Z端业务信息
        List<ProdAttr> zInfo = new ArrayList<>();
        List<ProdAttr> prodAttrList = JSONObject.parseArray(newKiteBatchConfig.getShowAttrJon(), ProdAttr.class);
        // 将属性分组包装
        packageAttrInfo(baseInfo, aInfo, zInfo, newKiteBatchConfig, prodAttrList);
        baseInfo.addAll(aInfo);
        baseInfo.addAll(zInfo);
        List<Map<String, Object>> tmplAttrJson = baseInfo.stream().map(prodAttr -> {
            Map<String, Object> map = new HashMap<>();
            map.put("cname", prodAttr.getCname());
            map.put("fieldName", prodAttr.getFieldName());
            return map;
        }).collect(Collectors.toList());
        return JSONObject.toJSONString(tmplAttrJson);
    }

    @Override
    public DataResult<Map<String, Object>> batchImport(InputStream inputStream, InputStream fileUploadStream, boolean isXls, String fileName, String goodsSku) throws Exception {
        // 处理excel
        String result = "";
        Map<String, Object> resultMap = new HashMap<>();
        try {
            List<List<String>> datas = getExcelData(inputStream, 0, 2, isXls);
            List<Map<String, Object>> resultList = BatchAcceptUtil.packageTmpAttr(goodsSku, datas);
            result = JsonUtil.list2Json(resultList);
            resultMap.put("tmplAttrJson", result);

            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            String objectName = UUID.randomUUID() +"."+suffix;
            aliyunOssService.upload(fileUploadStream, objectName);
            resultMap.put("tmplFileUrl", objectName);
        }
        catch (IOException e) {
            ErrorConsts.BUSI_COMMON_ERROR.throwOut(e, I18nUtil.getMessage("BSS_KITE_BATCH_000046"));
        }
        return ErrorConsts.SUCCESS.getResult(resultMap);
    }

    @Override
    public List<Map<String, Object>> getProdAttr(String goodsSku) {
        List<Map<String, Object>> result = new ArrayList<>();
        Offer offer = OfferCache.get(goodsSku);
        List<OfferDetail> offerDetails = offer.listOfferDetails();
        List<ProdAttr> prodAttrs = new ArrayList<>();
        for (OfferDetail offerDetail : offerDetails) {
            String prodId = offerDetail.getObjId();
            prodAttrs = ProdAttrCache.get(prodId);
            break;
        }
        if (KiteListUtils.isNotEmpty(prodAttrs)) {
            List<AttrDTO> keyAttrs = SpringUtil.getBean(IGoodsService.class).queryGoodKeyAttrsByProvince(goodsSku).getResultObject();
            List<String> keyAttrFileNames = Optional.ofNullable(keyAttrs).orElse(Lists.newArrayList()).stream()
                .map(AttrDTO::getAttrCode).collect(Collectors.toList());
            prodAttrs.forEach(prodAttr -> {
                if ((!KiteStringUtils.equals("E", prodAttr.getShowType()) || !KiteStringUtils.equals("H", prodAttr.getShowType()))
                    && !KiteStringUtils.isEmpty(prodAttr.getCname())) {
                    Map<String, Object> map = new HashMap<>();
                    map.put("cname", prodAttr.getCname());
                    map.put("fieldName", prodAttr.getFieldName());
                    map.put("groupId", prodAttr.getGroupId());
                    map.put("groupName", prodAttr.getGroupName());
                    map.put("attrId", prodAttr.getAttrId());
                    map.put("attrValueTypeId", prodAttr.getAttrValueTypeId());
                    if (keyAttrFileNames.contains(prodAttr.getFieldName())) {
                        map.put("keyAttr", true);
                    }
                    if (KiteStringUtils.equals(prodAttr.getIsNull(), "F")
                        && !KiteStringUtils.equals(prodAttr.getShowType(), KeyConsts.PROD_ATTR_SHOW_TYPE_E)) {
                        map.put("isNull", "F");
                    }
                    else {
                        map.put("isNull", "T");
                    }
//                    map.put("isNull", prodAttr.getIsNull());
                    result.add(map);
                }
            });
        }
//        List<ProdAttr> prodAttrList = SpringUtil.getBean(ProdAttrMapper.class).listProdAttrByProdId("60000457");
//        ProdInst prodInst = SpringUtil.getBean(ProdInstMapper.class).getProdInstByCompInstId("123", "123");
        return result;
    }

    @Override
    public KiteBatchConfig queryConfigByOfferId(String applyRegionCode,String cateId, String offerId, String fileName) {
        // 根据offerId可以确定一条记录
        Example example = new Example(KiteBatchConfig.class);
        Example.Criteria criteria = example.createCriteria().andEqualTo("applyRegionCode", applyRegionCode)
            .andEqualTo("offerId", offerId).andEqualTo("tmplFileName", fileName).andEqualTo("statusCd", "1000");
        if (KiteStringUtils.isNotBlank(cateId)) {
            criteria.andEqualTo("catagoryId", cateId);
        }
        return kiteBatchConfigMapper.selectOneByExample(example);
    }

    @Override
    public void checkTmplFileName(KiteBatchConfig kiteBatchConfig) {
        Example example = new Example(KiteBatchConfig.class);
        Example.Criteria criteria = example.createCriteria().andEqualTo("applyRegionCode", kiteBatchConfig.getApplyRegionCode())
            .andEqualTo("catagoryId", kiteBatchConfig.getCatagoryId())
            .andEqualTo("offerId", kiteBatchConfig.getOfferId())
            .andEqualTo("tmplFileName", kiteBatchConfig.getTmplFileName())
            .andEqualTo("createStaff", kiteBatchConfig.getCreateStaff())
            .andEqualTo("statusCd", "1000");
        if (Objects.nonNull(kiteBatchConfig.getConfigId())) {
            criteria.andNotEqualTo("configId", kiteBatchConfig.getConfigId());
        }
        List<KiteBatchConfig> kiteBatchConfigs = kiteBatchConfigMapper.selectByExample(example);
        if (!KiteListUtils.isEmpty(kiteBatchConfigs)) {
            ErrorConsts.BUSI_COMMON_ERROR.throwOut(
                "模板【"+ kiteBatchConfig.getTmplFileName() + "】已存在",
                "模板【"+ kiteBatchConfig.getTmplFileName() + "】已存在");
        }
    }

    @Override
    public void downloadTemplate(OutputStream outputStream, String objectName, String sceneInstId) throws IOException {
        if (KiteStringUtils.isBlank(objectName) || KiteStringUtils.equals("undefined", objectName)) {
            KiteBatchConfig kiteBatchConfig = getConfigBySceneInstId(sceneInstId);
            if (Objects.isNull(kiteBatchConfig)) {
                ErrorConsts.BUSI_COMMON_ERROR.throwOut("请配置下载模版");
            }
            objectName = kiteBatchConfig.getTmplFileUrl();
        }
        aliyunOssService.exportOssFileStream(outputStream, objectName);
    }

    @Override
    public void exportExcel(OutputStream outputStream, KiteBatchConfig kiteBatchConfig) {
        List<ProdAttr> prodAttrList = JSONObject.parseArray(kiteBatchConfig.getShowAttrJon(), ProdAttr.class);
        // 基础业务信息
        List<ProdAttr> baseInfo = new ArrayList<>();
        // A端业务信息
        List<ProdAttr> aInfo = new ArrayList<>();
        // Z端业务信息
        List<ProdAttr> zInfo = new ArrayList<>();
        // 将属性分组包装
        packageAttrInfo(baseInfo, aInfo, zInfo, kiteBatchConfig, prodAttrList);
        // 第一行表头
        List<String> headFirstList = baseInfo.stream().map(ProdAttr::getCname).collect(Collectors.toList());
        for (int i = 0; i < aInfo.size(); i++) {
            headFirstList.add("A端");
        }
        for (int i = 0; i < zInfo.size(); i++) {
            headFirstList.add("Z端");
        }
        // 第二行表头
        List<String> headSecondList = aInfo.stream().map(ProdAttr::getCname).collect(Collectors.toList());
        headSecondList.addAll(zInfo.stream().map(ProdAttr::getCname).collect(Collectors.toList()));
        // A/Z端省市区位置
        int aCol = -1;
        int zCol = -1;
        int cityCal = -1;

        // 枚举值位置
        Map<ProdAttr, Integer> attrIndex = new HashMap<>();
        // 表头位置，计算每个表头占的行数与列数
        List<MergedResult> headFirstMerge = new ArrayList<>();
        for (int i = 0; i < headFirstList.size(); i++) {
            String head = headFirstList.get(i);
            MergedResult mergedResult;
            if (KiteStringUtils.equals(head, "A端")) {
                mergedResult = getMergedResult(0, 0, baseInfo.size(), baseInfo.size() + aInfo.size() - 1);
            } else if (KiteStringUtils.equals(head, "Z端")) {
                mergedResult = getMergedResult(0, 0, baseInfo.size() + aInfo.size(), prodAttrList.size() - 1);
            } else {
                ProdAttr prodAttr = prodAttrList.stream().filter(attr -> KiteStringUtils.equals(head, attr.getCname()))
                    .findFirst().orElse(null);
                if (Objects.nonNull(prodAttr) && KiteStringUtils.equals(prodAttr.getAttrValueTypeId(), "98A")) {
                    attrIndex.put(prodAttr, i);
                }
                mergedResult = getMergedResult(0, 1, i, i);
            }
            headFirstMerge.add(mergedResult);

            // 部分产品没有AZ端
            if (KiteListUtils.isEmpty(headSecondList)) {
                // 获取“省市区”的列数
                if (KiteStringUtils.contains(headFirstList.get(i), "省市区")) {
                    if (KiteStringUtils.contains(headFirstList.get(i), "Z")) {
                        zCol = i;
                    } else {
                        aCol = i;
                    }
                } else if (KiteStringUtils.contains(headFirstList.get(i), "城市")) {
                    cityCal = i;
                }
            }
        }

        List<MergedResult> headSecondMerge = new ArrayList<>();
        for (int i = 0; i < headSecondList.size(); i++) {
            String head = headSecondList.get(i);
            // 获取“省市区”的列数
            if (KiteStringUtils.contains(head, "省市区")) {
                if (KiteStringUtils.contains(head, "Z")) {
                    zCol = baseInfo.size() + i;
                }
                else {
                    aCol = baseInfo.size() + i;
                }
            }
            else if (KiteStringUtils.contains(headFirstList.get(i), "城市")) {
                cityCal = i;
            }
            else {
                ProdAttr prodAttr = prodAttrList.stream().filter(attr -> KiteStringUtils.equals(head, attr.getCname()))
                    .findFirst().orElse(null);
                if (Objects.nonNull(prodAttr) && KiteStringUtils.equals(prodAttr.getAttrValueTypeId(), "98A")) {
                    attrIndex.put(prodAttr, baseInfo.size() + i);
                }
            }
            MergedResult mergedResult = getMergedResult(1, 1, baseInfo.size() + i, baseInfo.size() + i);
            headSecondMerge.add(mergedResult);
        }
        try {
            ExcelUtil.batchTempExportExcel(kiteBatchConfig.getTmplFileName(), headFirstList, headSecondList, headFirstMerge,
                headSecondMerge, outputStream, aCol, zCol, cityCal, kiteBatchConfig.getApplyRegionCode(), attrIndex);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 属性分组包装
     * @param prodId
     * @param baseInfo
     * @param aInfo
     * @param zInfo
     * @param prodAttrList
     */
    private void packageAttrInfo(List<ProdAttr> baseInfo, List<ProdAttr> aInfo, List<ProdAttr> zInfo,
                                 KiteBatchConfig kiteBatchConfig, List<ProdAttr> prodAttrList) {
//        Offer offer = OfferCache.get(kiteBatchConfig.getOfferId(), kiteBatchConfig.getApplyRegionCode());
        List<OfferDetail> offerDetails = OfferDetailCache.get(kiteBatchConfig.getOfferId(), kiteBatchConfig.getApplyRegionCode());
        String prodId = offerDetails.stream().filter(offerDetail -> KiteStringUtils.isNotEmpty(offerDetail.getObjId())).findFirst()
            .map(OfferDetail::getObjId).orElse(null);

        // 将A/Z端市、区、标准地址等去掉，省保留，导入时特殊处理；
        // mv将省也去掉
        prodAttrList.removeIf(prodAttr -> KiteStringUtils
            .equalsAny(prodAttr.getFieldName(), "2_0000221", "2_0000222", "2_0000109", "2_0000111", "200002618",
                "200002619") || KiteStringUtils.equals("10003665", prodId) && KiteStringUtils
            .equals(prodAttr.getFieldName(), "2_0000107"));
        // 修改”省“作为省市区，导入后通过属性处理类重新设置值
        prodAttrList.forEach(prodAttr -> {
            if (KiteStringUtils.equalsAny(prodAttr.getFieldName(), "2_0000220", "2_0000107")) {
                prodAttr.setCname(prodAttr.getCname().replace("省", "省市区"));
            }
        });
        Map<String, List<ProdAttr>> prodAttrGroup = KiteListUtils.groupBy(prodAttrList, "groupName");

        prodAttrGroup.forEach((k, v) -> {
            if (KiteStringUtils.contains(k, "A端")) {
                aInfo.addAll(v);
            }
            else if (KiteStringUtils.contains(k, "Z端")) {
                zInfo.addAll(v);
            }
            else {
                baseInfo.addAll(v);
            }
        });
    }

    @Override
    public void previewTemplate(OutputStream outputStream, KiteBatchConfig kiteBatchConfig) throws IOException {
//        InputStream in = aliyunOssService.exportOssFileStream(fileUrl);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        exportExcel(os, kiteBatchConfig);
        InputStream in = new ByteArrayInputStream(os.toByteArray());
        //加载Excel文档
        com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
        wb.loadFromStream(in);
        //获取第1个工作表
        com.spire.xls.Worksheet sheet = wb.getWorksheets().get(0);
        // 整个excel文件展示在pdf同一个页
        wb.getConverterSetting().setSheetFitToPage(true);

//        //调用方法保存文件为PDF格式
//        sheet.saveToPdf("ToPDF2.pdf");
        // 保存到流
        sheet.saveToPdfStream(outputStream);
//        //加载PDF
//        PdfDocument pdf = new PdfDocument();
//        pdf.loadFromFile("ToPDF2.pdf");
//        //保存为HTML格式
//        pdf.saveToFile("ToHTML.html", FileFormat.HTML);
    }

    public static List<List<String>> getExcelData(InputStream inputStream, int initialRow, int maxRowIndex, boolean isXls) throws IOException {
        List<List<String>> squareList = new ArrayList<List<String>>();
        Workbook workbook = null;
        if (isXls) {
            workbook = new HSSFWorkbook(inputStream); // 解析xls格式
        }
        else {
            workbook = new XSSFWorkbook(inputStream); // 解析xlsx格式
        }
        // 在Excel文档中，第一张工作表的缺省索引是0
        Sheet sheet = workbook.getSheetAt(0);
        // 循环行Row
        if (0 == maxRowIndex) {
            maxRowIndex = sheet.getLastRowNum();
        }
        for (int rowIndex = initialRow; rowIndex <= maxRowIndex; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            // 存放每行信息
            List<String> li = new ArrayList<String>();
            // 循环列
            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    li.add("");
                    continue;
                }
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        li.add(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            li.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()));
                        }
                        else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String s = cell.getStringCellValue();
                            if (s.indexOf('.') > -1) {
                                li.add(String.valueOf(new Double(s)));
                            }
                            else {
                                li.add(s.trim());
                            }
                        }
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        li.add(String.valueOf(cell.getBooleanCellValue()));
                        break;
                    default:
                        li.add(null);
                        break;
                }
            }
            squareList.add(li);
        }
        return squareList;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static MergedResult isMergedRegion(Sheet sheet, int row, int column) {
        MergedResult mergedResult = new MergedResult();
        boolean isMerged = false;//判断是否合并单元格

        mergedResult.setRowIndex(row);//判断的行
        mergedResult.setColumnIndex(column);//判断的列
        //获取sheet中有多少个合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并后的单元格
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow //判断行
                && column >= firstColumn && column <= lastColumn) {//判断列
                isMerged = true;
                mergedResult.setFirstRow(firstRow);
                mergedResult.setLastRow(lastRow);
                mergedResult.setFirstColumn(firstColumn);
                mergedResult.setLastColumn(lastColumn);
                mergedResult.setRowMergeNum(lastRow - firstRow + 1);
                mergedResult.setColumnMergeNum(lastColumn - firstColumn + 1);
                break;
            }
        }
        mergedResult.setMerged(isMerged);
        return mergedResult;
    }

    private KiteBatchConfig getConfigBySceneInstId(String sceneInstId) {
        KiteSceneInst sceneInst = querySceneInstService.getSceneInst(sceneInstId);
        KiteOfferInst offerInst = querySceneInstService.getOfferInst(sceneInst.getCustId(), sceneInst.getRootInstId());
        String spu = offerInst.getAttrValue("saleGoodsCode");
        if (KiteStringUtils.isEmpty(spu)) {
            spu = querySceneInstService.getExtParameterValue(sceneInstId, "saleGoodsCode");
        }
        String applyRegionCode = sceneInst.getProvinceNbr();
        return queryConfigByOfferId(applyRegionCode, null, spu, null);
    }

    private MergedResult getMergedResult(int firstRow, int lastRow, int firstColumn, int lastColumn) {
        MergedResult mergedResult = new MergedResult();
        mergedResult.setFirstRow(firstRow);
        mergedResult.setLastRow(lastRow);
        mergedResult.setFirstColumn(firstColumn);
        mergedResult.setLastColumn(lastColumn);
        return mergedResult;
    }

}
